import sqlite3 as sqlite 
# import collections

class Database(object):
    def __init__(self,db_path,check_same_thread=True):
        '''
        @param db_path sqlite数据库路径
        @check_same_thread 如果为True,将不允许多线程并发访问
        '''
        self.conn=sqlite.connect(db_path, check_same_thread=check_same_thread)
        self.cursor=self.conn.cursor()

    class DatabaseError(Exception):
        pass

    def __del__(self):
        '''删除时关闭conn
        '''
        self.conn.close()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def lastrowid(self):
        return self.cursor.lastrowid

    def execute(self,sql_command,*args,**kw):
        try:
            self.cursor.execute(sql_command,*args,**kw)
        except Exception as e:
            print(sql_command)
            print(e)
            raise Database.DatabaseError

    def fetchall(self):
        return self.cursor.fetchall()

    def fetchone(self):
        return self.cursor.fetchone()

    def commit(self):
        self.conn.commit()

    def get_cur_cols_name_list(self):
        col_name_list = [tuple[0] for tuple in self.cursor.description]
        return col_name_list

    def get_table_info(self,table_name):
        self.execute("PRAGMA table_info({})".format(table_name))
        lis=self.fetchall()
        return lis

    def get_tables_info(self,show=True):
        self.execute("SELECT * FROM sqlite_master")
        table_lis=self.fetchall()
        if(show):
            for ele in table_lis:
                print(ele)
        return table_lis

    def drop_table(self,table):
        self.execute("DROP TABLE IF EXISTS {0}".format(table))

    def create_table(self,table,table_info_lis):
        table_information=self.__tool_join_str(table_info_lis)
        self.drop_table(table)
        self.execute('''CREATE TABLE {0}({1})'''.format(table,table_information))


    def select(self,select_ele,table,addtional_info=None):
        if isinstance(select_ele,list):
            select_ele=self.__tool_join_str(select_ele)
        if(addtional_info==None):
            self.execute("SELECT {0} FROM {1}".format(select_ele,table))
        else:
            self.execute("SELECT {0} FROM {1} WHERE ({2})".format(select_ele,table,addtional_info))
        return self.fetchall()
    
    def insert(self,value_lis,table,head_lis=None):
        if(head_lis==None):head_lis=""
        else:head_lis="("+self.__tool_join_str(head_lis)+")"
        value_lis=str(tuple(value_lis)).replace(",)",")").replace("\\'","''")
        self.execute("INSERT INTO {0} {1} VALUES {2}".format(table,head_lis,value_lis))
                
    def insert_lis(self,value_lis_lis,table,head_lis=None):
        for value_lis in value_lis_lis:
            self.insert(value_lis,table,head_lis)

    def __tool_join_str(self,str_lis):
        return ",".join(str_lis)
